import { connection } from '../app/database/mysql';
import { signModel } from './sign.model';

/**
 * 获取用户签到记录
 */
export const getSign = async (userId: number) => {
  const statement = `
    SELECT * FROM user_sign
    WHERE user_id = ?
    ORDER BY id DESC
    LIMIT 7
  `;

  const [data] = await connection.promise().query(statement, userId);
  return data;
}

/**
 * 检查今天是否已经签到
 */
export const checkTodaySigned = async (userId: number): Promise<boolean> => {
  const statement = `
    SELECT COUNT(*) as count 
    FROM user_sign 
    WHERE user_id = ? 
    AND DATE(FROM_UNIXTIME(sign_date / 1000)) = CURDATE()
  `;

  const [rows] = await connection.promise().query(statement, userId);
  const result = rows as any[];
  return result[0]?.count > 0;
}

/**
 * 查询本周（周一到周日）的签到记录
 */
export const getSignWeekday = async (userId: number) => {
  const statement = `
    SELECT
      id,
      user_id,
      sign_date,
      -- 返回中文星期几
      CASE DAYOFWEEK(FROM_UNIXTIME(sign_date / 1000))
        WHEN 2 THEN '周一'
        WHEN 3 THEN '周二'
        WHEN 4 THEN '周三'
        WHEN 5 THEN '周四'
        WHEN 6 THEN '周五'
        WHEN 7 THEN '周六'
        WHEN 1 THEN '周日'
        ELSE '未知'
      END as weekday_name,
      -- 返回数字星期（1=周一，7=周日）
      CASE DAYOFWEEK(FROM_UNIXTIME(sign_date / 1000))
        WHEN 2 THEN 1
        WHEN 3 THEN 2
        WHEN 4 THEN 3
        WHEN 5 THEN 4
        WHEN 6 THEN 5
        WHEN 7 THEN 6
        WHEN 1 THEN 7
        ELSE 0
      END as weekday_number
    FROM user_sign
    WHERE user_id = ?
      -- 本周一00:00:00到本周日23:59:59
      AND sign_date >= UNIX_TIMESTAMP(
        DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)
      ) * 1000
      AND sign_date < UNIX_TIMESTAMP(
        DATE_ADD(
          DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY),
          INTERVAL 7 DAY
        )
      ) * 1000
    ORDER BY sign_date ASC
  `;

  const [data] = await connection.promise().query(statement, userId);
  return data;
}

/**
 * 创建签到记录
 */
export const createSign = async (post: signModel) => {
  const statement = `
    INSERT INTO user_sign
    SET ?
  `;

  const [data] = await connection.promise().query(statement, post);
  return data;
}